<?php
/*程序功能：mysql数据库备份功能*/
namespace app\console\controller;

use think\Request;
use think\Controller;
use app\console\model\Backup as BackupModel;

class DashboardCopy extends Controller
{
    public function index()
    {
        $dbname = 'app50';
        ini_set('max_execution_time', '0');
        ini_set('memory_limit', '1024M');//加上这两行,可以备份大数据,昨晚测了一下公司的一个600M的表,很快就可以备份成功
        $code_type = 'utf8';
        header("Content-type:text/html;charset=$code_type");
        header("Content-disposition:   filename=backup.sql");//所保存的文件名
        header("Content-type:   application/octetstream");
        header("Pragma:   no-cache");
        header("Expires:   0");
        $conn = mysqli_connect("127.0.0.1", "app50", "App@longcai.net");

        $db = mysqli_select_db($conn, $dbname);

        mysqli_query($conn, "SET NAMES 'utf8'");
        $flag = '1';//1:全部备份,2:只备份表结构,3:只备份数据
        $res = self::back_database($dbname, $conn, $flag);//备份数据库

        $to_file_name = "uploads" . DS . "mysql" . DS . "i_shop" . time() . ".sql";

        $myfile = fopen($to_file_name, "w") or die("Unable to open file!");

        fwrite($myfile, $res);
        fclose($myfile);
        $data = [
            'title'       => "Ishop数据库备份" . date('Y-m-d H:i:s', time()) . "版",
            'url'         => Request::instance()->domain() . DS . $to_file_name,
            'create_time' => time(),
        ];
        $operation = (new BackupModel())->allowField(true)->save($data);

        if ($operation) {
            return ['code' => 200];
        } else {
            return ['code' => 404];
        }

    }


    function back_database($dbname, $conn, $flag = '1')
    {
        $crlf = "\r\n";
        $str = '';
        $now = date('Y年m月d日H点i分s秒', time());
        $str .= "$crlf--$crlf-- 数据库: `$dbname`$crlf--$crlf-- 导出日期: `$now`$crlf--$crlf";
        $str .= "$crlf--$crlf-- 作者: ****$crlf--$crlf-- QQ: 1019822077$crlf--$crlf";
        $str .= "-- blog: http://hi.baidu.com/woaidelphi/blog$crlf--$crlf";
        $str .= "SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";$crlf";

        $tables = mysqli_query($conn, "SHOW TABLES FROM " . $dbname);

        // $tables = mysqli_list_tables($dbname, $conn);
        $num_tables = mysqli_num_rows($tables);//表的总数
        //将这些表记录到一个数组(取得表名)
        $tabList = array();
        while ($row = mysqli_fetch_row($tables)) {
            $tabList[] = $row[0];
        }

        $i = 0;
        foreach ($tabList as $table) {
            if ($flag == '1' or $flag == '2') {
                $query = mysqli_query($conn, "SHOW CREATE TABLE $table");
                $row = mysqli_fetch_row($query);
                $str .= "-- --------------------------------------------------------$crlf$crlf";
                $str .= "--$crlf-- 表的结构 `$table` $crlf--$crlf";
                $str .= $row[1] . ";$crlf--$crlf";
            }
            //备份表内容
            if ($flag == '1' or $flag == '3') {
                $str .= "--$crlf-- 导出表中的数据 `$table` $crlf--$crlf";
                $str .= self::get_table_content($dbname, $table, $conn);
                $str .= "$crlf$crlf";
            }
        }


        return $str;
    }

    //得到表中的记录sql
    function get_table_content($dbname, $table, $conn)
    {
        $crlf = "\r\n";
        $schema_create = "";
        $temp = "";
        // $result = mysqli_db_query($conn, "SELECT   *   FROM   $table");
        $result = mysqli_query($conn, "SELECT   *   FROM   $table");
        $i = 0;
        while ($row = mysqli_fetch_row($result)) {
            $schema_insert = "INSERT   INTO   $table   VALUES   (";
            for ($j = 0; $j < mysqli_num_fields($result); $j++) {
                if (!isset($row[$j]))
                    $schema_insert .= "   NULL,";
                elseif ($row[$j] != "")
                    $schema_insert .= "   '" . addslashes($row[$j]) . "',";
                else
                    $schema_insert .= "   '',";
            }
            $schema_insert = preg_replace("{,$}", "", $schema_insert);
            $schema_insert .= ");$crlf";
            $temp = $temp . $schema_insert;
            $i++;
        }
        return $temp;
    }
}






